<?php
namespace app\api\controller\execl;

use app\common\controller\Api;
use app\api\model\wanlshop\Order;
use app\api\model\wanlshop\KuaidiSub;
use app\api\model\wanlshop\Goods;
use app\api\model\wanlshop\Refund;
use app\api\model\wanlshop\Kuaidi;
use app\api\model\wanlshop\Shop;
use app\api\model\wanlshop\OrderAddress;
use app\api\model\wanlshop\PayOutTrade;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;
use think\Log;
use addons\wanlshop\library\WanlSdk\Ehund; //快递100订阅
use think\Db;
use think\Exception;
use think\Validate;
use think\Request;
use think\exception\PDOException;
use think\exception\ValidateException;

use addons\wanlshop\library\WanlChat\WanlChat;
use app\common\controller\Wanlshop;
use vod\Request\V20170321\GetMediaAuditResultTimelineRequest;

class Export extends Api
{
    protected $model = null;
    protected $noNeedLogin = '*';
    protected $noNeedRight='*';
    public function order(){
        $state = input('state',2);
        $date = input('date','2023-10-27');
        $good_id = input('good');
        $startOfDayTimestamp = strtotime($date . ' 00:00:00');
        $endOfDayTimestamp = strtotime($date . ' 23:59:59');
        $orderQuery = Order::hasWhere('neworderGoods',function ($query)use($good_id){
            if ($good_id){
                $query->where('goods_id',$good_id);
            }
        })->with(['newpay', 'neworderGoods', 'newaddress'])
            ->alias('order')
            ->where('order.state', $state)
            ->where('order.createtime', '>=', $startOfDayTimestamp)
            ->where('order.createtime', '<=', $endOfDayTimestamp);
//        if($good_id){
//            $orderQuery->neworderGoods()->where('goods_id',$good_id);
//        }

        $order = $orderQuery->select();

        // 创建一个新的Excel工作簿
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        // 添加表头
        $sheet->setCellValue('A1', '订单id');
        $sheet->setCellValue('B1', '微信订单号');
        $sheet->getColumnDimension('B')->setWidth(25);
        $sheet->getStyle('B1')->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER);
        $sheet->setCellValue('C1', '订单号');
        $sheet->getColumnDimension('C')->setWidth(25);
        $sheet->setCellValue('D1', '手机号');
        $sheet->getColumnDimension('D')->setWidth(15);
        $sheet->setCellValue('E1', '订单状态');
        $sheet->setCellValue('F1', '姓名');
        $sheet->setCellValue('G1', '地址');
        $sheet->getColumnDimension('G')->setWidth(35);
        $sheet->setCellValue('H1', '详细地址');
        $sheet->getColumnDimension('H')->setWidth(35);
        $sheet->setCellValue('I1', '订单时间');
        $sheet->setCellValue('J1', '支付金额');
        $sheet->setCellValue('K1', '退款金额');
        $sheet->setCellValue('L1', '商品信息');
        $sheet->setCellValue('M1', '核销码链接');
        // 填充数据
        $row = 2;
        foreach ($order as $item) {
            switch ($item->state){
                case 1: $state = '待支付';
                    break;
                case 2: $state = '待发货';
                    break;
                case 3: $state = '待收货';
                    break;
                case 4: $state = '待评论';
                    break;
                case 5: $state = '售后订单(已弃用)';
                    break;
                case 6: $state = '已完成';
                    break;
                case 7: $state = '已取消';
                    break;
            }
            $sheet->setCellValue('A' . $row, $item->id);
            $sheet->setCellValueExplicit('B' . $row, $item->pay['trade_no']??'',\PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
            $sheet->setCellValueExplicit('C' . $row, $item->order_no,\PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
            $sheet->setCellValueExplicit('D' . $row, $item->address['mobile']??'',\PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
            $sheet->setCellValue('E' . $row, $state);
            $sheet->setCellValue('F' . $row, $item->address['name']??'');
            $sheet->setCellValue('G' . $row, $item->address['address']??'');
            $sheet->setCellValue('H' . $row, $item->address['address_name']??'');
            $sheet->setCellValue('I' . $row, date('Y-m-d H:i:s', $item->createtime));
            $sheet->setCellValue('J' . $row, $item->pay['price']??'');
            $sheet->setCellValue('K' . $row, $item->pay['refund_price']??'');
            $sheet->setCellValue('L' . $row, json_encode($item->ordergoods,JSON_UNESCAPED_UNICODE));
            $sheet->setCellValue('M' . $row, "https://xinweidu.enjoysix.com/qr?QRtype=tongcheng&id=".$item->order_no);
            $row++;
        }
        // 导出Excel文件
        $writer = new Xlsx($spreadsheet);
        $writer->save("order_export{$date}.xlsx");
        $this->success('获取成功',"https://xinweidu.enjoysix.com/order_export{$date}.xlsx");
    }
    //execl表格导入发货信息
    public function information(){
        $Rdata = [];
        $fail = [];
        // 获取上传的文件
        $file = request()->file('sendGoods');
        if($file){
            // 保存上传的文件到服务器的临时目录
            $info = $file->move('upload/'); // 保存到'upload/'目录
            if ($info) {
                $filePath = $info->getRealPath(); // 获取文件的实际路径
                //解析 Excel 表格
                $spreadsheet = IOFactory::load($filePath);
                $worksheet = $spreadsheet->getActiveSheet();
                $data = $worksheet->toArray();
                // 查找收货地址列的索引
                $phoneIndex = null;
                $waybill = null;
                if ($data) {
                    $headerRow = array_shift($data); // 获取表头
                    foreach ($headerRow as $index => $header) {
                        if (strpos($header, '收件人手机') !== false) { // 字样的列为收件人手机/电话号
                            $phoneIndex = $index;
                        }
                        elseif (strpos($header, '运单号') !== false) {
                            $waybill = $index;
                        }
                    }
                }
                if($phoneIndex !== null){
                    // 遍历 Excel 数据并修改数据库
                    foreach ($data as $indexs=>$row) {
                        $phone = $row[$phoneIndex]; // 获取手机号的值
                        $waybillValue = $row[$waybill];
                        $orderAddress = OrderAddress::where('mobile',$phone)->find();
                        if($orderAddress){ //如果匹配到
                            $order = Order::where('id',$orderAddress->order_id)->find();

                            if($order){
                                if($order->state === 2){  //是否是待发货状态
                                    $config = get_addon_config('wanlshop'); // 获取店铺配置信息
                                    $ehund = new Ehund($config['kuaidi']['secretKey'], $config['ini']['appurl'] . $config['kuaidi']['callbackUrl']); // 初始化快递100接口
                                    $is_express_no = model('app\index\model\wanlshop\KuaidiSub')->where(['express_no' => $waybillValue])->count();
                                    if ($config['kuaidi']['secretKey'] && $is_express_no == 0) {
                                        $returncode = $ehund->subScribe( 'youzhengguonei', $waybillValue, $orderAddress->mobile);

                                        if ($returncode['returnCode'] != 200) {
                                             Log::error('快递订阅接口异常-' . $returncode['message']);
                                        }
                                        $express = [
                                            'sign' => $ehund->sign($waybillValue),
                                            'express_no' => $waybillValue,
                                            'returncode' => $returncode['returnCode'],
                                            'message' => $returncode['message']
                                        ];


                                    }
                                    $this->pushOrder($order->id, '已发货'); // 推送消息
                                    $result = false;
                                    Db::startTrans();
                                    try {
                                        $order->state = 3;
                                        $order->express_name = 'youzhengguonei';
                                        $order->express_no = $waybillValue;
                                        $order->delivertime = time();
                                        $result = $order->save();
                                        // 写入快递订阅列表
                                        if ($express) KuaidiSub::create($express);
                                        Db::commit();
                                    } catch (PDOException $e) {
                                        Db::rollback();
                                        $this->error($e->getMessage());
                                    } catch (Exception $e) {
                                        Db::rollback();
                                        $this->error($e->getMessage());
                                    }
                                     //判断是否支付成功
                                    if($result){
                                        $Rdata[] = $indexs.'发货成功:'.$order->order_no.',手机号:'.$orderAddress->mobile.','.'订单状态:'.$order->state;
                                        Log::error('发货成功:'.$order->order_no.'手机号:'.$orderAddress->mobile);
                                    }else{
                                        $Rdata[] = $indexs.'发货失败！:'.$order->order_no.',手机号:'.$orderAddress->mobile.','.'订单状态:'.$order->state;
                                        Log::error('网络异常，发货失败');
                                    }
                                }else{
                                    $Rdata[] = $indexs.'订单状态不是待发货:'.$order->order_no.',手机号:'.$orderAddress->mobile.','.'订单状态:'.$order->state;
                                    if($order->state >3){
                                        $fail[] = $indexs.'订单状态不是待发货:'.$order->order_no.',手机号:'.$orderAddress->mobile.','.'订单状态:'.$order->state;
                                    }
                                    Log::error('订单状态不是待发货:'.$order->order_no.',手机号:'.$orderAddress->mobile);
                                }
                            }else{
                                $Rdata[] = $indexs.'未匹配到订单:'.$orderAddress;
                                Log::error('未匹配到订单:'.$orderAddress);
                            }
                        }else{
                            $Rdata[] = $indexs.'未匹配到地址:'.$orderAddress;
                            Log::error('未匹配到地址:'.$orderAddress);
                        }

                    }
                    $all = [
                      'all'=> $Rdata,
                      'stataFail'=>$fail,
                    ];
                    $temporaryFilePath = $info->getRealPath(); // 获取临时文件的路径
                    unlink($temporaryFilePath); // 删除临时文件
                    return $this->success('请求成功', $all);
                }
            }

        }
    }
    private function pushOrder($order_id = 0, $state = '已发货')
    {
        $order = Order::where('id',$order_id)->find();
        $orderGoods = model('app\index\model\wanlshop\OrderGoods')
            ->where(['order_id' => $order_id])
            ->select();
        $msgData = [];
        foreach ($orderGoods as $goods) {
            $msg = [
                'user_id' => $order['user_id'], // 推送目标用户
                'shop_id' => $order['shop_id'],
                'title' => '您的订单'.$state, // 推送标题
                'image' => $goods['image'], // 推送图片
                'content' => '您购买的商品 '.(mb_strlen($goods['title'],'utf8') >= 25 ? mb_substr($goods['title'],0,25,'utf-8').'...' : $goods['title']).' '.$state,
                'type' => 'order',  // 推送类型
                'modules' => 'order',  // 模块类型
                'modules_id' => $order_id,  // 模块ID
                'come' => '订单'.$order['order_no'] // 来自
            ];
            $msgData[] = $msg;
            $wanlchat = new WanlChat();
            $wanlchat->send($order['user_id'], $msg);
        }

        $notice = model('app\index\model\wanlshop\Notice')->saveAll($msgData);
    }


    public function import(Request $request){
        // 获取上传的文件
        $rule = [
            'file' => 'require|file',
        ];
        $message = [
            'file.require' => '文件不能为空',
            'file.file'    => '请上传有效的文件',
        ];
        $validate = new Validate($rule, $message);
        $file = $request->file('file');
        if (!$validate->check(['file' => $file])) {
            $errors = $validate->getError();
            // 处理验证失败
            return $this->error($errors);
        }
        if($file){
            // 保存上传的文件到服务器的临时目录
            $info = $file->move('upload/'); // 保存到'upload/'目录
            //解析 Excel 表格
            $filePath = $info->getRealPath(); // 获取临时文件的路径
            $spreadsheet = IOFactory::load($filePath);
            $worksheet = $spreadsheet->getActiveSheet();
            $data = $worksheet->toArray();
            var_dump($data);

            unlink($filePath); // 删除临时文件
        }
    }
    //发货导入 （订单号查询）
    public function newInformation(Request $request){
        // 获取上传的文件
        $file = request()->file('file');
        $fail = [];
        $success = 0;
        $kuaidi =   input('post.kuaidi');
        if($file&&$kuaidi){
            $info = $file->move('upload/'); // 保存到'upload/'目录
            if($info){
                $filePath = $info->getRealPath(); // 获取文件的实际路径
                //解析 Excel 表格
                $spreadsheet = IOFactory::load($filePath);
                $worksheet = $spreadsheet->getActiveSheet();
                $data = $worksheet->toArray();
                $rowCount = count($data);
                if ($rowCount > 901) {
                    return $this->error('文件行数超过900条，无法处理！');
                }else{
                    $orderIndex = null; //订单号
                    $waybill = null;//运单号
                    if ($data) {
                        $headerRow = array_shift($data); // 获取表头
                        foreach ($headerRow as $index => $header) {
                            if (strpos($header, '订单号') !== false) {
                                $orderIndex = $index;
                            }
                            elseif (strpos($header, '运单号') !== false) {
                                $waybill = $index;
                            }
                        }
                    }
                    if(isset($orderIndex) && isset($waybill)){
                        // 遍历 Excel 数据并修改数据库
                        foreach ($data as $indexs=>$row) {
                            $order_no = $row[$orderIndex]; // 获取订单号的值
                            $express_no = $row[$waybill];
                            $order = Order::where('order_no',$order_no)->with(['newpay','newaddress'])->find();
                            if($order){
                                //订单存在
                                if($order->state === 2){ //是否是待发货状态
                                    $config = get_addon_config('wanlshop'); // 获取店铺配置信息
                                    $ehund = new Ehund($config['kuaidi']['secretKey'], $config['ini']['appurl'] . $config['kuaidi']['callbackUrl']); // 初始化快递100接口
                                    $is_express_no = model('app\index\model\wanlshop\KuaidiSub')->where(['express_no' => $express_no])->count();
                                    if ($config['kuaidi']['secretKey'] && $is_express_no == 0) {
                                        $returncode = $ehund->subScribe($kuaidi, $express_no, $order->newaddress->mobile);

                                        if ($returncode['returnCode'] != 200) {
                                            Log::error('快递订阅接口异常-' . $returncode['message']);
                                        }
                                        $express = [
                                            'sign' => $ehund->sign($express_no),
                                            'express_no' => $express_no,
                                            'returncode' => $returncode['returnCode'],
                                            'message' => $returncode['message']
                                        ];
                                    }
                                    $result = false;
                                    Db::startTrans();
                                    try {
                                        $order->state = 3;
                                        $order->express_name = $kuaidi;
                                        $order->express_no = $express_no;
                                        $order->delivertime = time();
                                        $result = $order->save();
                                        // 写入快递订阅列表
                                        if ($express) KuaidiSub::create($express);
                                        Db::commit();
                                    } catch (PDOException $e) {
                                        Db::rollback();
                                        $this->error($e->getMessage());
                                    } catch (Exception $e) {
                                        Db::rollback();
                                        $this->error($e->getMessage());
                                    }
                                    //数据库更新操作是否成功
                                    if($result){
                                        $success++; //成功条数自动增加
                                    }else{
                                        //订单状态不是待发货 记录失败
                                        $fail[] = [
                                            'order_no'=>$order_no,
                                            'state'=>$order->state,
                                            'pay_no'=>$order->newpay->trade_no,
                                            'msg'=>'数据库更新操作失败'
                                        ];
                                    }
                                }else{
                                    //订单状态不是待发货 记录失败
                                    $fail[] = [
                                        'order_no'=>$order_no,
                                        'state'=>$order->state,
                                        'pay_no'=>$order->newpay->trade_no,
                                        'msg'=>'订单状态不是待发货'
                                    ];
                                }

                            }else{
                                //订单不存在 记录数据
                                $fail[] = [
                                    'order_no'=>$order_no,
                                    'msg'=>'未查询到该笔订单'
                                ];
                            }
                        }
                        $data = [
                            'successNum'=>$success,
                            'fail'=>$fail
                        ];
                        unlink($filePath); // 删除临时文件
                        return $this->success('导入成功',$data);
                    }else{
                        return $this->error('请传正确的Execl,表头格式不对,必须有订单号与运单号');
                    }
                }
            }
        }
    }
    //获取快递列表
    public function kuaidi(){
        $kuaidi = Kuaidi::all();
        return $this->success('获取成功',$kuaidi);
    }
    public function getGoods(){
        $shop_id = Shop::where('user_id',$this->auth->id)->find();
        $goods = Goods::where('shop_id',$shop_id->id)->select();
        return $this->success('获取成功',$goods);
    }
    public function wxExport(){

        $state = input('state',3);
        $date = input('date','2023-10-27');
        $order = Order::with(['newpay','neworderGoods','newaddress'])
            ->where('state',"$state")
            ->where('createtime','>',strtotime($date))
            ->where('createtime','<',strtotime($date) + 86400)
            ->whereNotNull('express_no')
            ->select();
        // 创建一个新的Excel工作簿
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        // 添加表头
        $sheet->setCellValue('A1', '交易单号');
        $sheet->setCellValue('B1', '商户单号');
        $sheet->setCellValue('C1', '商户号');
        $sheet->setCellValue('D1', '发货方式');
        $sheet->setCellValue('E1', '发货模式');
        $sheet->setCellValue('F1', '快递公司');
        $sheet->setCellValue('G1', '快递单号（多个快递单使用;分隔）');
        $sheet->setCellValue('H1', '是否完成发货');
        $sheet->setCellValue('I1', '是否重新发货');
        $sheet->setCellValue('J1', '商品信息');
        $sheet->setCellValue('K1', '状态');
        $columnWidths = [
            'A' => 30, // 交易单号
            'B' => 30, // 商户单号
            'C' => 15, // 商户号
            'D' => 15, // 发货方式
            'E' => 15, // 发货模式
            'F' => 15, // 快递公司
            'G' => 20, // 快递单号
            'H' => 15, // 是否完成发货
            'I' => 15, // 是否重新发货
            'J' => 30, // 商品信息
            'K' => 15, // 状态
        ];
        foreach ($columnWidths as $column => $width) {
            $sheet->getColumnDimension($column)->setWidth($width);
        }

        // 填充数据
        $row = 2;
        foreach ($order as $item) {
            $payOutTrade = PayOutTrade::where('pay_id',$item->newpay->id)->where('order_id',$item->id)->order('id','desc')->find();
            if ($payOutTrade->type == 1) { //判断是否是火脸
                $tran_no = $payOutTrade->transaction_id;
            }else{
                $tran_no = $item->newpay->trade_no;
            }
            if ($payOutTrade->mch_id){
                $mch_id = $payOutTrade->mch_id;
            }elseif(time()>1700118180){
                $mch_id = '605810106'; //乐刷线上
            }else{
                $mch_id = '605810124';  //乐刷
            }
//            else{
//                $mch_id = '605810124';
//            }
            $kuaidi = Kuaidi::where('code',$item->express_name)->find();
            $fahuo = '统一发货';
            $goodName = $item->neworderGoods->title.'*'.$item->neworderGoods->number;
            switch ($item->state){
                case 1: $state = '待支付';
                    break;
                case 2: $state = '待发货';
                    break;
                case 3: $state = '待收货';
                    break;
                case 4: $state = '待评论';
                    break;
                case 5: $state = '售后订单(已弃用)';
                    break;
                case 6: $state = '已完成';
                    break;
                case 7: $state = '已取消';
                    break;
            }

            $sheet->setCellValue('A' . $row, $tran_no);
            $sheet->setCellValue('B' . $row, $payOutTrade->out_trade_no??'');
            $sheet->setCellValue('C' . $row, $mch_id);
            $sheet->setCellValue('D' . $row, '物流快递');
            $sheet->setCellValue('E' . $row, $fahuo);
            $sheet->setCellValue('F' . $row, $kuaidi->name??'');
            $sheet->setCellValue('G' . $row, $item->express_no??'');
            $sheet->setCellValue('H' . $row, '是');
            $sheet->setCellValue('I' . $row, '');
            $sheet->setCellValue('J' . $row, $goodName??'');
            $sheet->setCellValue('K' . $row, $state);
            $style = $sheet->getStyle('G'.$row);
            $style->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER);
            $row++;
        }
        // 导出Excel文件
        $writer = new Xlsx($spreadsheet);
        $writer->save("wx{$date}.xlsx");
        $this->success('获取成功',"https://xinweidu.enjoysix.com/wx{$date}.xlsx");
    }
    public function refund(){
        $state = input('state','0');
        $date = input('date','2023-10-27');
        $startOfDayTimestamp = strtotime($date . ' 00:00:00');
        $endOfDayTimestamp = strtotime($date . ' 23:59:59');
        $order = Refund::with(['order','pay','log','goods'])
            ->alias('refund')
            ->where('refund.state', $state)
            ->where('refund.createtime', '>=', $startOfDayTimestamp)
            ->where('refund.createtime', '<=', $endOfDayTimestamp)
            ->select();
        // 创建一个新的Excel工作簿
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        // 添加表头
        $sheet->setCellValue('A1', '退款ID');
        $sheet->setCellValue('B1', '退款状态');
        $sheet->setCellValue('C1', '物流状态');
        $sheet->setCellValue('D1', '支付订单号');
        $sheet->setCellValue('E1', '订单状态');
        $sheet->setCellValue('F1', '退款金额');
        $sheet->setCellValue('G1', '退款理由');
        $sheet->setCellValue('H1', '退款类型');
        $sheet->setCellValue('I1', '退货理由');
        $sheet->setCellValue('J1', '付款时间');
        $sheet->setCellValue('K1', '退款理由记录');
        $sheet->setCellValue('L1', '退款产品');
        $sheet->getColumnDimension('D')->setWidth(25);
        $sheet->getColumnDimension('G')->setWidth(25);
        $sheet->getColumnDimension('J')->setWidth(25);
        $sheet->getColumnDimension('K')->setWidth(30);
        $sheet->getColumnDimension('L')->setWidth(55);
        // 填充数据
        $row = 2;
        foreach ($order as $item) {
            switch ($item->state){
                case 0: $state = '申请退款';
                    break;
                case 1: $state = '卖家同意';
                    break;
                case 2: $state = '卖家拒绝';
                    break;
                case 3: $state = '申请平台介入';
                    break;
                case 4: $state = '成功退款';
                    break;
                case 5: $state = '退款已关闭';
                    break;
                case 6: $state = '已提交物流';
                    break;
                case 7: $state = '第三方退款中';
                    break;
                case 8: $state = '退款失败';
                    break;
            }
            switch ($item->reason){
                case 0: $reason = '不喜欢';
                    break;
                case 1: $reason = '空包裹';
                    break;
                case 2: $reason = '一直未送达';
                    break;
                case 3: $reason = '颜色/尺码不符';
                    break;
                case 4: $reason = '质量问题';
                    break;
                case 5: $reason = '少件漏发';
                    break;
                case 6: $reason = '假冒品牌';
                    break;

            }
            switch ($item->order->state){
                case 1: $orderState = '待支付';
                    break;
                case 2: $orderState = '待发货';
                    break;
                case 3: $orderState = '待收货';
                    break;
                case 4: $orderState = '待评论';
                    break;
                case 5: $orderState = '售后订单(已弃用)';
                    break;
                case 6: $orderState = '已完成';
                    break;
                case 7: $orderState = '已取消';
                    break;
            }
            $sheet->setCellValue('A' . $row, $item->id);
            $sheet->setCellValue('B' . $row, $state??'');
            $sheet->setCellValue('C' . $row, $item->expressType == 1?'已收到货':'未收到货');
            $sheet->setCellValueExplicit('D' . $row, $item->pay->trade_no??'',\PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
            $sheet->setCellValue('E' . $row, $orderState??'');
            $sheet->setCellValue('F' . $row, $item->price??'');
            $sheet->setCellValue('G' . $row, $item->refund_content??'');
            $sheet->setCellValue('H' . $row, $item->type==1?'退货退款':'我要退款(无需退货)');
            $sheet->setCellValue('I' . $row, $reason??'');
            $sheet->setCellValue('J' . $row, date('Y-m-d H:i:s', $item->order->paymenttime));
            $sheet->setCellValue('K' . $row, $item->log->content??'');
            $sheet->setCellValue('L' . $row, $item->goods->title??'');
            $row++;
        }
        // 导出Excel文件
        $writer = new Xlsx($spreadsheet);
        $writer->save("refund_export{$date}.xlsx");
        $this->success('获取成功',"https://xinweidu.enjoysix.com/refund_export{$date}.xlsx");
    }
}